package com.studymaster.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


public class CreateSqlLite {
	
	private  List<List<Map<String, String>>> mList=new ArrayList<List<Map<String,String>>>();
	private  String URL="jdbc:sqlite:/";
	private  String user = "admin"; 
	private  String password = "123456";
	private  String forname="org.sqlite.JDBC";
	public CreateSqlLite(String path)
	{
		URL+=path;
	}
	public void createDb() throws SQLException
	{
		System.out.println("开始创建数据库"+System.currentTimeMillis());
		//创建数据库对象
        Connection conn = null;
        Statement stmt = null;
        try {
			Class.forName(forname);
			 conn = DriverManager.getConnection("jdbc:sqlite:/d:/sqlite/server.db");
			 stmt = conn.createStatement();
			  deleteAlltable(stmt);
			  insertAllTable(stmt);
			  System.out.println(" 完成");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        finally{
        	if(stmt!=null)
        	{
        		stmt.close();
        	}
        	if(conn!=null)
        	{
        		stmt.close();
        	}
        }
	}

	public void deleteAlltable(Statement stmt) throws SQLException
	{
		//删除学校表中的所有记录
		String sql="delete from school";
		stmt.executeUpdate(sql);
		//删除学院的所有记录
		sql="delete from academy";
		stmt.executeUpdate(sql);
		//删除专业的所有记录
		sql="delete from profession";
		stmt.executeUpdate(sql);
		//删除班级的所有信息
		sql="delete from class";
		stmt.executeUpdate(sql);
		//删除班级的所有信息
		sql="delete from user_detail";
	    stmt.executeUpdate(sql);
	}
	
	public void insertAllTable(Statement stmt) throws SQLException
	{
		List<Map<String, String>> list;
		String sql="";
		GetList g=new GetList();
		//添加学校
		sql="select id,name from school";
		list=g.getList(sql);
		this.mList.add(list);
		//添加学院
		sql="select id,school_id,name from academy";
		list=g.getList(sql);
		this.mList.add(list);
		//添加专业
		sql="select id,name,academy_id from profession";
		list=g.getList(sql);
		this.mList.add(list);
		//添加班级
		sql="select id,name,profession_id from class";
		list=g.getList(sql);
		this.mList.add(list);
		//添加用户信息
		sql="select user_id,person_id,username,college,academy,profession,class,picture,phone,email,qq,state,remarks from user_detail";
		list=g.getList(sql);
		this.mList.add(list);
		for(int i=0;i<this.mList.size();i++)
		{
			if(i==0)
			{
				addSchool(stmt,mList.get(i));
			}
			else if(i==1)
			{
				addAcademy(stmt,mList.get(i));
			}
			else if(i==2)
			{
				addProfession(stmt, mList.get(i));
			}
			else if(i==3)
			{
				addClass(stmt, mList.get(i));
			}
			else if(i==4)
			{
				addUserDetail(stmt, mList.get(i));
			}
		}
	}
	public void addSchool(Statement stmt,List<Map<String, String>> list) throws SQLException
	{
		String sql="";
		Map map=null;
		for(int i=1;i<list.size();i++)
		{
			map=list.get(i);
			sql="insert into school (id,name) values ("+map.get("id")+",'"+map.get("name")+"') ";
			//System.out.println(sql);
			stmt.executeUpdate(sql);
		}
	}
	
	public void addAcademy(Statement stmt,List<Map<String, String>> list) throws SQLException
	{
		String sql="";
		Map map=null;
		for(int i=1;i<list.size();i++)
		{
			map=list.get(i);
			sql="insert into academy (id,school_id,name) values("+map.get("id")+","+map.get("school_id")+",'"+map.get("name")+"') ";
//			System.out.println(sql);
			stmt.executeUpdate(sql);
		}
	}
	public void addProfession(Statement stmt,List<Map<String, String>> list) throws SQLException
	{
		String sql="";
		Map map=null;
		for(int i=1;i<list.size();i++)
		{
			map=list.get(i);
			sql="insert into profession (id,name,academy_id) values("+map.get("id")+",'"+map.get("name")+"',"+map.get("academy_id")+") ";
			stmt.executeUpdate(sql);
		}
	}
	public void addClass(Statement stmt,List<Map<String, String>> list) throws SQLException
	{
		String sql="";
		Map map=null;
		for(int i=1;i<list.size();i++)
		{
			map=list.get(i);
			sql="insert into class (id,name,profession_id) values("+map.get("id")+",'"+map.get("name")+"',"+map.get("profession_id")+") ";
			stmt.executeUpdate(sql);
		}
	}
	public void addUserDetail(Statement stmt,List<Map<String, String>> list) throws SQLException
	{
		String sql="";
		Map map=null;
		for(int i=1;i<list.size();i++)
		{
			map=list.get(i);
			sql="insert into user_detail (user_id,person_id,college,academy,profession,class,picture,phone,email,qq,state,remarks) values('"
			+map.get("user_id")+"','"
			+map.get("person_id")+"','"
			+map.get("username")+"',"
			+map.get("college")+","
			+map.get("academy")+","
			+map.get("profession")+","
			+map.get("class")+",'"
			+map.get("picture")+"','"
			+map.get("phone")+"','"
			+map.get("email")+"','"
			+map.get("qq")+"',"
			+map.get("state")+",'"
			+map.get("remarks")
			+"') ";
			//System.out.println(sql);

			stmt.executeUpdate(sql);
		}
	}
}
